package context.dao;

import context.model.Food;
import context.model.FoodSchool;
import context.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class RankingListRepository {
    private final String INSERT_FOOD = "insert into rankingList(foodname,CalorificValue) values(?,?)";

    private JdbcTemplate jdbcTemplate;

    @Autowired          //自动装配
    public RankingListRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

//    private User mapRow(ResultSet resultSet, int i) throws SQLException {
//        User result = new User();
//        result.setId(resultSet.getLong("foodname"));
//        result.setId(resultSet.getLong("NO"));
//        result.setId(resultSet.getLong("CalorificValue"));
//        return result;
//    }

    //增
    public int insertFood(final String foodname, final Long CalorificValue) {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                        PreparedStatement ps = jdbcTemplate.getDataSource()
                                .getConnection().prepareStatement(INSERT_FOOD, new String[]{"foodname", "CalorificValue"});
                        ps.setString(1, foodname);
                        ps.setLong(2, CalorificValue);
                        return ps;
                    }
                }, keyHolder);
        System.out.println("自动插入id============================" + keyHolder.getKey().intValue());
        return keyHolder.getKey().intValue();
    }

    //删
    private static final String sql = "delete from rankingList set foodname = ?";

    public void deleteRankingList(String foodname) {
        Object[] obj = new Object[]{foodname};
        int i = jdbcTemplate.update(sql, obj);

        if (i > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }

    //查
    private final String SORT = "SELECT * FROM contest.rankinglist order by CalorificValue desc limit 10";

    public List<Food> findFoodByFoodname() {
        List<Food> foodList = new ArrayList<>();
        try {
            Connection connection = jdbcTemplate.getDataSource().getConnection(); //获得数据库连接

            PreparedStatement preparedStatement = connection.prepareStatement(SORT);  //创建预备语句
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Food result = new Food();
                result.setFoodname(resultSet.getString("foodname"));
                result.setCalorificValue(resultSet.getLong("CalorificValue"));
                foodList.add(result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return foodList;
    }

    //改
    private static final String sql1 = "update rankingList set CalorificValue=? where  foodname= ? ";

    public void updateRankingList(long CalorificValue, String foodname) {
        Object args[] = new Object[]{CalorificValue, foodname};
        int temp = jdbcTemplate.update(sql1, args);
        if (temp > 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败");
        }

    }
}
